package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Dorm;

public class DormControl {
	
	/**
	 * 根据寝室名，寝室楼id获取寝室寝室id
	 * @param dorm_name
	 * @param building_id
	 * @return
	 */
	public int getDormId(String dorm_name, int building_id) {
		DBConnect dbc = new DBConnect();
		String sql="{call getDormId('"+dorm_name+"',"+building_id+")}";
		/*
		 * String sql = "select * from dormitory,building " +
		 *	"where dormitory.dorm_building = building.building_id " +
		 *	"and dormitory.dorm_name = '"+ dorm_name +"' " +
		 *		"and building.building_id = "+ building_id +"";
		 */
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				int dorm_id = rs.getInt("dorm_id");
				return dorm_id;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			dbc.close();
		}
		return 0;
	}
	
	/**
	 * 根据管理员id获取其管理下的所有寝室信息
	 * @param admin_id
	 * @return
	 */
	public ArrayList<Dorm> getDorm(int admin_id){
		DBConnect dbc = new DBConnect();
		String sql="{call getDorm("+admin_id+")}";
		/*
		 * String sql = "select * from dormitory where dorm_building in (select building_id from building,admin " +
		 *	"                        where admin.admin_building = building.building_id and " +
		 *															"admin_id = "+ admin_id +")";
		 */
																
		ArrayList<Dorm> list = new ArrayList<Dorm>();
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				Dorm dorm = new Dorm();
				dorm.setDorm_id(rs.getInt("dorm_id"));
				dorm.setDorm_name(rs.getString("dorm_name"));
				list.add(dorm);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
}
